﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Ledict.Core.Data;
using System.Data.Common;
using Ledict.ConditionExtract.Model;
using Ledict.ConditionExtract.Common;

namespace Ledict.ConditionExtract.DAL
{
    public class WordBankKeyWordsDAL
    {
        private string DbName;
        public WordBankKeyWordsDAL()
        {
            DbName = "JudgeDoc";
        }
        NewIDDAL newiddal = new NewIDDAL();
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(SYS_WordBank_KeyWords Model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from SYS_WordBank_KeyWords");
            strSql.Append(" where WB_ID=@WB_ID and  WBK_Length=@WBK_Length and  WBK_Word=@WBK_Word ");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@WB_ID", DbType.String, Model.WB_ID);
            oBase.AddInParameter(oCmd, "@WBK_Word", DbType.String, Model.WBK_Word);
            oBase.AddInParameter(oCmd, "@WBK_Length", DbType.Int32, Model.WBK_Length);
            if (Convert.ToInt32(oBase.ExecuteScalar(oCmd)) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(SYS_WordBank_KeyWords Model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into SYS_WordBank_KeyWords(");
            strSql.Append("WBK_ID,WB_ID,WBK_Word,WBK_Length, WBK_UseCount)");
            strSql.Append(" values (");
            strSql.Append("@WBK_ID,@WB_ID,@WBK_Word,@WBK_Length,1)");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@WBK_ID", DbType.String, Model.WBK_ID);
            oBase.AddInParameter(oCmd, "@WB_ID", DbType.String, Model.WB_ID);
            oBase.AddInParameter(oCmd, "@WBK_Word", DbType.String, Model.WBK_Word);
            oBase.AddInParameter(oCmd, "@WBK_Length", DbType.Int32, Model.WBK_Length);

            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string WBK_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from SYS_WordBank_KeyWords ");
            strSql.Append(" where WBK_ID=@WBK_ID");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@WBK_ID", DbType.String, WBK_ID);

            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }


        public bool Update(SYS_WordBank_KeyWords Model)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update SYS_WordBank_KeyWords set ");
            strSql.Append("WBK_Word=@WBK_Word, WBK_Length=@WBK_Length");
            strSql.Append(" where WBK_ID=@WBK_ID");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());

            oBase.AddInParameter(oCmd, "@WBK_Word", DbType.String, Model.WBK_Word);
            oBase.AddInParameter(oCmd, "@WBK_Length", DbType.Int32, Model.WBK_Length);
            oBase.AddInParameter(oCmd, "@WBK_ID", DbType.String, Model.WBK_ID);

            if (oBase.ExecuteNonQuery(oCmd) > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 获得数据列表（要统计表述中的引用频次）
        /// </summary>
        public DataTable GetList_WB_IDAndWhere(string WB_ID, string whereTest)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"         
		  create table #tAll(WBK_ID  varchar(50),WB_Name  nvarchar(100), WBK_Word nvarchar(200) 
		  ,WBK_Length int, ElementCount int )
		  insert  into #tAll 
          select      
          WBK_ID,(select WB_Name from dbo.SYS_WordBank  where   WB_ID =a.WB_ID) as WB_Name ,
          WBK_Word,WBK_Length,(select  Count(*)
          from dbo.SYS_Express_WordBankRel where  WBK_ID=a.WBK_ID) 
          as ElementCount 
          FROM SYS_WordBank_KeyWords  as a  
          where WB_ID=@WB_ID");
            if (whereTest != "")
                strSql.Append(" and  WBK_Word like '%'+@whereTest+'%' ");

            strSql.Append(@" select   row_number()over(order by ElementCount desc) as showindex,WBK_ID, WB_Name ,
          WBK_Word,WBK_Length,ElementCount FROM  #tAll 
 
          drop table #tAll");
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@WB_ID", DbType.String, WB_ID);
            oBase.AddInParameter(oCmd, "@whereTest", DbType.String, whereTest);
            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }


        }


        /// <summary>
        /// 获得数据列表（要统计表述中的引用频次）
        /// </summary>
        public string GetList_WB_ID(string WB_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"  
		  create table #tAll(WB_ID  varchar(50),WBK_Word  nvarchar(200), ElementCount int )
		  insert  into #tAll 
            select WB_ID, a.WBK_Word
           , (select  Count(*) from dbo.SYS_Express_WordBankRel where  WBK_ID=a.WBK_ID) as ElementCount  
             from dbo.SYS_WordBank_KeyWords as a 
            where WB_ID=@WB_ID  order by ElementCount  desc
          
           declare @AllWBK_Word nvarchar(max)
           set @AllWBK_Word=''
           select  @AllWBK_Word=@AllWBK_Word+ ','+isnull(WBK_Word,'')
           + '('+cast(ElementCount as varchar(10))+')'  from #tAll 
           where WB_ID=@WB_ID  order by ElementCount desc
            select substring(@AllWBK_Word,2,len(@AllWBK_Word))
         drop table #tAll
            ");

            SYS_WordBank_KeyWords model = new SYS_WordBank_KeyWords();
            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@WB_ID", DbType.String, WB_ID);

            return LdFormat.ObjectToString(oBase.ExecuteScalar(oCmd));
        }


        /// <summary>
        /// 获得数据列表（不统计表述中的引用频次）
        /// </summary>
        public DataTable GetList_WB_IDNoOrder(string WB_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"         
		 
          select  WBK_ID, WBK_Word
          FROM SYS_WordBank_KeyWords   
          where WB_ID=@WB_ID");

            Database oBase = DatabaseFactory.CreateDatabase(DbName);
            DbCommand oCmd = oBase.GetSqlStringCommand(strSql.ToString());
            oBase.AddInParameter(oCmd, "@WB_ID", DbType.String, WB_ID);

            DataTable OtB = oBase.ExecuteDataSet(oCmd).Tables[0];
            if (OtB.Rows.Count > 0)
            {
                return OtB;
            }
            else
            {
                return new DataTable();
            }


        }


    }
}
